clear all
set more off

local data_dir ./data/cleaned
local raw_dir ./data/raw

mkf fy2005tip_auth
cwf fy2005tip_auth
import excel "`raw_dir'/tip/FY 2005 TIP Report on WIC Authorized Vendors.xls", sheet("FY 05 WIC Authorized Vendors") firstrow case(lower) allstring clear

foreach yr in 06 07 08 09 10 11{
		mkf fy20`yr'tip_auth
		cwf fy20`yr'tip_auth
		import excel "`raw_dir'/tip/FY 20`yr' TIP Report on Authorized Vendors.xlsx", ///
			sheet("FY `yr' WIC Authorized Vendors") firstrow case(lower) allstring clear
	}
forval yr = 2012/2016{
		mkf fy`yr'tip_auth
		cwf fy`yr'tip_auth
		import excel "`raw_dir'/tip/FY `yr' TIP Report on Authorized Vendors.xlsx", ///
			sheet("FY `yr' WIC Authorized Vendors") firstrow case(lower) allstring clear
	}

mkf fy2017tip_auth
cwf fy2017tip_auth
import excel "`raw_dir'/tip/2017-WIC-44 National Report - Vendor Info.xls", firstrow ///
	case(lower) allstring clear
rename (vendorstreetnumber vendorstreet vendoradditionaladdress vendorcity vendorstate vendorzip) (streetnumber streetname additionaladdressinfo city state zipcode)
drop vendorzipsuffix

mkf fy2018tip_auth
cwf fy2018tip_auth
import excel "`raw_dir'/tip/2018-WIC-44 National Report - Vendor Info.xls", firstrow ///
	case(lower) allstring clear
rename (vendorstreetnumber vendorstreet vendoradditionaladdress vendorcity vendorstate vendorzip) (streetnumber streetname additionaladdressinfo city state zipcode)
drop vendorzipsuffix

cwf fy2005tip_auth
frame put _all, into(tip_auth)
cwf tip_auth
forval year = 2006/2018{
		frameappend fy`year'tip_auth
	}

foreach var of varlist _all{
	replace `var' = strtrim(stritrim(strtrim(stritrim(`var'))))
}
destring fiscalyear, replace
*rename fiscalyear year
foreach var of varlist vendorname streetnumber streetname additional city{
		replace `var' = proper(`var')
	}
drop j-n
gen sstreetadd = streetnumber + " " + streetname + " " + additional

*example address 701 McMeans Ave, Bay Minette, AL 36507, USA
gen fadd = streetnumber + " " + streetname + " " + additional + "," + " " + city + "," + " " + state + " " + zipcode + "," + " " + "USA"
replace fadd = subinstr(stritrim(fadd), " , ", ", ", .)
drop if missing(fiscalyear) | vendortype == "Home Food Delivery Contractor" | vendortype == "Direct Distribution Center"

save `data_dir'/tip_auth, replace

use `data_dir'/tipstorescountiesdbase.dta, clear
rename name county_name
rename countyfp county_fip
rename geoid fips_5
rename statefp state_fip
rename STUSAB state
rename latitude slat
rename longitude slong
save `data_dir'/tipstorescountieslatlong.dta, replace
duplicates tag fadd, generate(dup_add)
gsort - dup_add fadd fid
*only 33 duplicates, drop
drop if dup_add > 0
*assume that obs that don't merge from geocode (6%) are mistakes somehow, and that should keep all obs from tip_auth
merge 1:m fadd using `data_dir'/tip_auth
drop if _merge == 1
egen vendor_type = group(vendortype)
labmask vendor_type, values(vendortype)
drop _merge ALAND AWATER dup_add state_fip fid county_fip coord vendortype
destring fips_5, generate(fips)
rename fips_5 fips_string
destring zipcode, generate(zip)
rename zipcode zip_string
save `data_dir'/geocode_tip_auth, replace



/*
state key file names

AK_key.csv    DE_key.csv  KY_key.csv  MT_key.csv  OH_key.csv  UT_key.csv
AL_key.csv    FL_key.csv  LA_key.csv  NC_key.csv  OK_key.csv  VA_key.csv
GA_key.csv  MA_key.csv  ND_key.csv  OR_key.csv  VT_key.csv
AR_key.csv    HI_key.csv  MD_key.csv  NE_key.csv  PA_key.csv  WA_key.csv
AZ_key.csv    IA_key.csv  ME_key.csv  NH_key.csv  RI_key.csv  WI_key.csv
CA_key.csv    ID_key.csv  MI_key.csv  NJ_key.csv  SC_key.csv  WV_key.csv
CO_key.csv    IL_key.csv  MN_key.csv  NM_key.csv  SD_key.csv  WY_key.csv
CT_key.csv    IN_key.csv  MO_key.csv  NV_key.csv  TN_key.csv
DC_key.csv    KS_key.csv  MS_key.csv  NY_key.csv  TX_key.csv

allyears.csv  
in "./tipdata/statekeys/
cd ./tipdata/statekeys/
local filelist: dir . files "*.csv"
di `filelist'

foreach file of local filelist{
		import delimited "./`file'", asdouble clear
		di `file'
		local name subinstr("`file'", ".csv", "", .)
		di `name'
	}

